from peewee import *
from sanic import Sanic
# from app import config
import random
import datetime

# from .Permissions import Permission
from Permissions import Permission

# Create a proxy for our db.
db = DatabaseProxy()

# Use proxy for our DB.
class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    class Meta:
        table_name = 'user'
    
    id = AutoField()
    name = CharField(unique=True, verbose_name='用户帐号')
    pwd = CharField(verbose_name='用户密码')
    role = CharField(verbose_name='用户角色')
    balance = FloatField(default=0.0, verbose_name='用户金额')
    last_login = DateTimeField(default=datetime.datetime.now)
    login_count = IntegerField(default=0)

    auths = CharField(verbose_name='用户权限', default=Permission.default())

    def __str__(self):
        return f'<{self.auths} = {self.id} : {self.name} : {self.pwd} : {self.role} : {self.balance} : {self.last_login} : {self.login_count} >'
    
    def asdict(self):
        return {'id': self.id, 'name': self.name, 'pwd': self.pwd, 'role': self.role, 'balance': self.balance, 'last_login': self.last_login, 'login_count': self.login_count, 'auths': self.auths}

class Host(BaseModel):
    class Meta:
        table_name = 'host'
    
    id = AutoField()
    ip = CharField(unique=True, verbose_name='主机ipv4地址')
    area = CharField(unique=True, verbose_name='主机物理地址区域')
    login_url = CharField(verbose_name='虚拟主机登录地址')
    
    server_state = IntegerField(default=200, verbose_name='主机服务器状态 200正常 ?xx不正常')
    
    server_type = IntegerField(default=2, verbose_name='服务器类型(0api 1自营 2国内 3国外 4不限内容 5CDN 6vpn)')

    server_provider = CharField(verbose_name='ISP服务商名')
    server_url = CharField(verbose_name='服务器登录地址')
    security_code = CharField(verbose_name='服务器ep安全码')
    server_account = CharField(verbose_name='服务器帐号')
    server_password = CharField(verbose_name='服务器密码')

    def __str__(self):
        return f'<{self.id} : {self.ip}: {self.area} : {self.server_state}: {self.login_url} : {self.server_type} : {self.server_provider} : {self.security_code} : {self.server_account} : {self.server_password}>'

    def asdict(self):
        return {'id': self.id,
                'ip': self.ip,
                'area': self.area,
                'login_url': self.login_url,

                'server_state': self.server_state,
                'server_type': self.server_type,

                'server_provider': self.server_provider,
                'server_url': self.server_url,
                'security_code': self.security_code,
                'server_account': self.server_account,
                'server_password': self.server_password,
        }

class Order(BaseModel):
    class Meta:
        table_name = 'order'
    
    id = AutoField()
    
    host_name = CharField(verbose_name='主机帐号')
    host_pwd = CharField(verbose_name='主机密码')
    host_size = IntegerField(verbose_name='主机大小')
    db_size = IntegerField(verbose_name='数据库大小')

    # host_start = DateTimeField(default = datetime.datetime.now, verbose_name='订单开始时间')
    # host_end = DateTimeField(default = datetime.datetime.now, verbose_name='订单结束时间')

    # host_state = IntegerField(default=0, verbose_name='当前虚拟主机状态')

    create_time = DateTimeField(default=datetime.datetime.now)
    update_time = DateTimeField(default=datetime.datetime.now)

    owner = ForeignKeyField(User, column_name='user_id', backref='orders',  on_delete='Cascade', on_update='Cascade', verbose_name='订单外鍵')
    hoster = ForeignKeyField(Host, column_name='host_id', backref='orders',  on_delete='Cascade', on_update='Cascade', verbose_name='订单外鍵')
    
    def __str__(self):
        return f'<{self.id} : {self.host_name} : {self.host_pwd} : {self.host_size} : {self.db_size} : {self.create_time} : {self.update_time} >'


database = SqliteDatabase('idc.db')
# database = MySQLDatabase('idc', host='localhost', port=3306, user='root', password='root')

db.initialize(database)

if __name__ == '__main__':
    print(f'-' * 10)
    
    # db.create_tables([User, Host, Order], safe=True)
    User.create_table(safe=True)
    Host.create_table(safe=True)

    Order.create_table(safe=True)

    user_datas = [
        {'name': 'abc','pwd': 'abc', 'role': 'admin','balance':'3.0', 'last_login': datetime.datetime.now()},
        {'name': '123','pwd': '123', 'role': 'admin','balance':'4.0', 'last_login': datetime.datetime.now()},
        {'name': 'ccc','pwd': 'ccc', 'role': 'admin','balance':'5.0', 'last_login': datetime.datetime.now()},
    ]
    # 批量插入
    user_insert_sql = User.insert_many(user_datas).execute()

    # 左移(0api 1自营 2国内 3国外 4不限内容 5CDN 6vpn)
    host_datas = [
        {'ip' : '154.86.26.108', 'area' : '香港②', 'login_url': 'http://154.86.26.108:3312/vhost/index.php?c=session&a=loginForm', 'server_type': 1 << 0, 'server_provider': 'api王增杰2133282552', 'server_url': 'http://ep.2arik.cn', 'security_code' : 'Eg2uY6fweP4cDe7PUuYZVbPGvIKcXEAajfRn3gviEk1iGMvG', 'server_account':'858125124@qq.com', 'server_password':'Shujia2007'},
        {'ip' : '180.76.177.207', 'area' : '北京', 'login_url': 'http://180.76.177.207:3312/vhost/index.php?c=session&a=loginForm', 'server_type': 1 << 1,'server_provider': '百度云小常', 'server_url': 'http://180.76.177.207:3312/admin/?c=session&a=loginForm', 'security_code' : 'EuDmKWHRSn5XmcTc', 'server_account':'root', 'server_password':'guojia'},
    ]
    host_insert_sql = Host.insert_many(host_datas).execute()

    # 单个插入-原子操作
    a_user = {'name': 'abc_' + str(random.randint(1, 99)),'pwd': 'abc', 'role': 'admin','balance':'3.0', 'last_login': datetime.datetime.now()},
    
    # with db.atomic():
        # User.insert(a_user).execute()
    
    users = User.select()
    for user in users:
        print(f'user : {user} .')
    
    user = User.get_or_none(name='cccc')
    if user == None:
        iid = User.insert(name='cccc', pwd='ccc',role='admin', balance=33.3,last_login=datetime.datetime.now()).execute()
        print(f'insert return id : {iid}....')
    else:
        # User.delete_instance(user, recursive=True)
        # Order.delete().where(Order.user_id.in_([user.id])).execute()
        pass

    host = Host.get_or_none(area='美国')
    if host == None:
        iid = Host.insert(ip='180.76.177.222', 
                            area='美国',
                            login_url='http://180.76.177.222:3312/vhost/index.php?c=session&a=loginForm',
                            
                            server_type = ( ( 1 << 4 ) | ( 1 << 3 ) ),
                            server_provider = '007IDC',
                            server_url= 'https://idc.skin',
                            security_code = 'security_code',
                            server_account = 'server_account',
                            server_password = 'server_password').execute()
        print(f'insert return id : {iid}....')
    else:
        # Host.delete_instance(host, recursive=True)
        # Host.delete().where(Host.id.in_([host.id])).execute()
        pass

    # 左移(0api 1自营 2国内 3国外 4不限内容 5CDN 6vpn)
    host = Host.get_or_none(area='北京')
    if host != None:
        print(f'find {host}.')

        hid = (Host
                .insert(host.asdict())
                .on_conflict(
                    conflict_target=[Host.id],
                    preserve=[],
                    update={Host.server_type: ( ( 1 << 1 ) | ( 1 << 2 ) )})
                    .execute()
                )

        print(f'hid : {hid} .')
        nrows = (Host.update(server_type = ( ( 1 << 4 ) | ( 1 << 3 ) ))
                    .where(Host.area == '美国')
                    .execute()
                )
        print(f'nrows : {nrows} .')

    print(f'uuuuuuupdate' * 10)

    order = Order.get_or_none(id=13)
    if order == None:
        print(f'get_or_none id : {order}....')
    else:
        Order.delete_instance(order, recursive=True)
        # Host.delete().where(Host.id.in_([host.id])).execute()


    user = User.get_or_none(name='ccc')
    if user != None:
        print(f'find {user.name}.')
        user.balance = user.balance + 1
        user.name = 'ccc'
        # user.save()
        print(f'find {user}.')

        now = datetime.datetime.now()
        rowid = (User
                # .insert(name='ccc', pwd='ccc', role='admin')
                .insert(user.asdict())
                .on_conflict(
                    conflict_target=[User.id],  # Which constraint?
                    # preserve=[User.pwd, User.role],  # Use the value we would have inserted.
                    preserve=[],  # Use the value we would have inserted.
                    update={User.balance: User.balance + 1, User.last_login: now, User.login_count: User.login_count + 1})
                    .execute()
                )

        print(f'rowid : {rowid} .')
        nrows = (User.update(last_login = now,
                            login_count = User.login_count + 1)
                    .where(User.name == 'ccc')
                    # .execute()
                )
        print(f'nrows : {nrows} .')
    for user in users:
        print(f'user : {user} .')

    print(f'new' * 10)

    
    orders = Order.select()
    a_page = orders.paginate(2, 3)
    ccc = orders.count()
    print(f'一页 {a_page.execute()}')
    print(f'一页 {a_page}')
    # results = [model_to_dict(row) for row in a_page.iterator()]
    for p in a_page:
        print(f'page a : {p.host_name} id : {p.id}')

    print(f'总数量 {ccc} a page 数量{a_page.count()}')

    orders.execute()
    print(f'oooooorders : {orders}')
    for order in orders:
        print(f'ooooorder : {order} .')
        print(f'owner : {order.owner}')
        print(f'hoster : {order.hoster} {order.hoster.area}')
    
    # a_users = User.select().where(User.name == "ccc")

    try:
        a_user = User.get_or_none(User.name == "123")
        a_host = Host.get_or_none(Host.area == '香港')
        if a_user != None and a_host != None:
            # Two Method Insert Way As Same as
            # Order.create(owner=a_user,
            #             host_ip='0.0.0.0', 
            #             host_name='new',
            #             host_pwd='new',
            #             host_size=100,
            #             db_size=100,
            #             host_area='北京',
            #             login_url='0.0.0.0/login.html',
            #             create_time=datetime.datetime.now(),
            #             update_time=datetime.datetime.now())
            o_id = Order.insert(owner=a_user,
                        hoster=a_host,

                        # host_ip=a_host.ip, 
                        host_name='new',
                        host_pwd='new',
                        host_size=100,
                        db_size=100,
                        # host_area=a_host.area,
                        # login_url=a_host.login_url,
                        create_time=datetime.datetime.now(),
                        update_time=datetime.datetime.now()).execute()
            print(f'order id insert: {o_id}')
    except User.DoesNotExist:
        a_user = None
        a_host = None
        print(f'a_user : {a_user}')
        print(f'a_host : {a_host}')

    # same as below
    a_user = User.get_or_none(User.name == "cccc")
    print(f'a_user : {a_user}')

    # d = User.delete().where(User.name == "ccc").execute()
    # print(f'd : {d}')
    # d = Order.delete().where(a_user).execute()
    # print(f'd : {d}')

    # # 先删除该用户下的订单
    # users = User.select(User.id).where(User.name == "ccc")
    # print(f'org sql users : {users}')
    # print(f'count : {users.count()} by id....')
    # d= Order.delete().where(Order.user_id.in_(users)).execute()
    # print(f'd : {d} ........i in id')
    # # 然后删除用户
    # d = User.delete().where(User.name == "ccc").execute()
    # print(f'delte user  : {d}')

    users = User.select().where(User.name == "ccc")
    print(f'org sql users : {users}')
    print(f'count : {users.count()}')

    for user in users:
        print(f'user : {user}')
        for order in user.orders:
            print(f'order: {order}')
    
    print(f'-' * 10)
